package plgdb

import (
	"dgo/MyProj/PrjDBUtils"
	db_utils "dgo/goutils/db-utils"
	"dgo/goutils/utils"
	"fmt"
	"gitee.com/ymofen/supervalue"
	"math"
	"strings"
)

func (this *PlgDBBase) DBResQryList(qryParam *PlgDBQryParam) (errcode int, err error) {

	dbres := qryParam.DBResConf
	if dbres == nil {
		dbres = this.GetDBResConf(qryParam.ResID)
		qryParam.DBResConf = dbres
	}
	dbres = qryParam.HttpReq.DoResConfig(dbres)

	if dbres == nil {
		errcode = ERR_IN_PARAM_INVALID
		err = fmt.Errorf("找不到对应的资源[%s]", qryParam.ResID)
		return
	}

	dbres.CheckStrFunc("__datasource", func(strv string) {
		qryParam.DataSourceid = strv
	})

	if len(qryParam.DataSourceid) == 0 {
		qryParam.DataSourceid = this.Default_datasourceid
	}

	dbname := qryParam.DBName
	var dao *PrjDBUtils.DAOHelper
	if len(dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(qryParam.DataSourceid, dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(qryParam.DataSourceid)
	}
	err = dao.PrepareDBConn()
	if err != nil {
		errcode = ERR_DB_ERR
		return
	}
	qryParam.Dao = dao

	dao.MaxPackageRowCount = this.DefaultMaxPackageRowCount

	if this.OnBeforeQryRequestFunc != nil {
		if !this.OnBeforeQryRequestFunc(qryParam) {
			return
		}
	}

	if qryParam.HttpReq.BeforeQryRequestFunc != nil {
		if !qryParam.HttpReq.BeforeQryRequestFunc(qryParam) {
			return
		}
	}

	sqlconf := dbres.ValueByName(fmt.Sprintf("$sel-%s", dao.DriverName))
	if sqlconf == nil {
		sqlconf = dbres.ValueByName("$sel")

		if sqlconf == nil {
			return ERR_INNER, fmt.Errorf("配置非法[%s],找不到对应的[$sel]配置", qryParam.ResID)
		}
	}

	checkconf := sqlconf.ValueByName("check")
	if checkconf == nil {
		checkconf = dbres.ValueByName("check")
	}
	if ok, itm := this.checkVarByResConf(checkconf, qryParam.GetParamVal); !ok {
		errcode = ERR_IN_PARAM_INVALID
		code := itm.IntByName("errcode", 0)
		msg := itm.StringByName("msg", "")
		if code != 0 {
			errcode = code
		}
		err = fmt.Errorf(msg)
		return
	}

	qryParam.TraceSQL = sqlconf.IntByName("__tracesql", 0)

	dao.DBConn.OnAfterExecuteSQLFunc = func(sql string, err error, args ...interface{}) {
		qryParam.HttpReq.OnAfterExecSQL(qryParam.TraceSQL, sql, err, args...)
	}

	sqltype := sqlconf.IntByName("type", 0)
	if sqltype == 0 {
		return this.innerQryResType0(qryParam, sqlconf)
	} else if sqltype == 1 {
		return this.innerQryResType1(qryParam, sqlconf)
	} else if sqltype == 2 {
		return this.innerQryResType2(qryParam, sqlconf)
	}

	return
}

/*
*

	"$sel":{
	   "__tracesql":1,    // 响应时返回sql
	   "type":0,
	   "fields":"id, name, uptime",
	   "sql":"select $fields$ from $table$",    // 主体SQL语句,可以写视图,或者关联表, $field$将会替换 fields设置的值. 分离设定主要为了统计总记录数时进行替换成count(1)
	   "whereflag":1,    // 表名sql中是否本身存在where语句, 1:存在, 0:不存在  默认自动检测sql是否存在where
	   "args":{
	      "where":{     // where条件
	          "id":"fid='$id$'",      // $id$将会替换成传入的参数值, 如果传入args.id="001", 则会进行条件拼接  where fid='001'
	          "searchval":"name like '$searchval$%' or remark like '$searchval$'"
	       },
	      "conf":{    // 参数配置
	         "table":{"must": 1}    // must表示 table参数为必须传递的参数, 这里配置的参数, 最终会替换sql中的$table$内容
	       },
	      "__fixed":[    // 固定条件
	          "fstatus=1"
	       ]
	    },
	   "orderby":" id desc",         // 预置排序, 如果传入,则进行忽略
	   "jsonfields":""    // 指明字段中是否有json字段, 如果指明则响应数据时会处理成 json响应, 多个字段用 ","分割
	},
*/
func (this *PlgDBBase) innerQryResType0(qryParam *PlgDBQryParam, sqlconf *wrapper.SuperValue) (errcode int, err error) {
	sql_args_conf := sqlconf.ValueByName("args")

	sql_templ := sqlconf.StringByName(fmt.Sprintf("sql-%s", qryParam.Dao.DriverName), "")
	if len(sql_templ) == 0 {
		sql_templ = sqlconf.StringByName("sql", "")
	}

	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	hasWhereFlag := false
	if !sqlconf.CheckExistsIntFunc("whereflag", func(val int64) {
		hasWhereFlag = val == 1 // 如果有设定直接读取设定的值
	}) {
		hasWhereFlag = strings.Contains(strings.ToLower(sql_templ), "where") // 自动判断
	}

	if qryParam.Resp == nil {
		qryParam.Resp = wrapper.NewSVObject()
	}

	dao := qryParam.Dao
	args := qryParam.Args
	resp := qryParam.Resp

	singlekeyname := qryParam.SingleName
	lstkeyname := DEFAULT_LIST_KEYNAME

	page := qryParam.ReqIntByName(KEY_PAGE_INDEX, -1)
	pagesize := qryParam.ReqIntByName(KEY_PAGE_SIZE, DEFAULT_PAGE_SIZE)
	reqtotal := qryParam.ReqIntByName(KEY_REQ_CNT, 1)
	qry_posi := qryParam.ReqIntByName(KEY_POSI, -1)
	qry_num := qryParam.ReqIntByName(KEY_POSI_NUM, DEFAULT_PAGE_SIZE)

	var innererr error = nil

	_, _, err = dao.QueryCustom(func() string {
		if len(qryParam.SingleName) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func(sb *db_utils.SQLBuilder) {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}

		sb.AddAny(sql)
		if hasWhereFlag {
			sb.Whereflag = 1
		}

	}, func(wb *db_utils.SQLBuilder) {

		fixedCondi := wrapper.GetValueByName(sql_args_conf, "__fixed")
		if fixedCondi != nil {
			fixedCondi.Range(func(key string, val *wrapper.SuperValue) bool {
				wb.CheckAnd()
				wb.AddAny(val.String())
				return true
			})
		}

		if args == nil || args.Count() == 0 {
			return
		}

		args_where_conf := wrapper.GetValueByName(sql_args_conf, "where")
		if args_where_conf == nil {
			args_where_conf = sql_args_conf
		}
		if args_where_conf == nil {
			return
		}
		args_where_conf.Range(func(key string, val *wrapper.SuperValue) bool {
			if key == "__fixed" {
				return true
			}
			v1 := args.StringByName(key, "")
			if len(v1) == 0 {
				if ok, v2 := qryParam.GetParamVal(key); ok {
					v1 = fmt.Sprintf("%v", v2)
				}
			}
			if len(v1) > 0 {
				var str string
				if val.IsObject() {
					str = val.StringByName("value", "")
				} else {
					str = val.String()
				}
				str = strings.ReplaceAll(str, fmt.Sprintf("$%s$", key), v1)
				wb.CheckAnd()
				wb.AddAny(str)

			}
			return true
		})

	}, func(sb *db_utils.SQLBuilder) {
		orderby := qryParam.ReqStringByName("orderby", "")
		if len(orderby) == 0 && sqlconf != nil {
			orderby = sqlconf.StringByName("orderby", "")
		}
		if len(orderby) > 0 {
			sb.OrderBy().AddAny(orderby)
		}
	}, func() (posi int, num int) {
		if qry_posi >= 0 {
			return qry_posi, qry_num
		}
		if page >= 1 {
			return (page - 1) * pagesize, pagesize
		}
		return 0, 0
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string) {
		args_conf := wrapper.GetValueByName(sql_args_conf, "conf") //sql_args_conf.ValueByName("conf")
		if args_conf == nil {
			return
		}
		args_conf.Range(func(key string, val *wrapper.SuperValue) bool {
			stype := val.StringByName("type", "repl")

			strv := ""
			if ok, v := dao.GetParamVal(key); ok {
				strv = fmt.Sprintf("%v", v)
			} else {
				strv = args.StringByName(key, "") // 获取传入的值
			}

			if len(strv) == 0 && val.BoolByName("must", false) {
				innererr = fmt.Errorf("[%s] 必须指定一个值", key)
				return false
			}

			if stype == "repl" {

				*sql = strings.ReplaceAll(*sql, fmt.Sprintf("$%s$", key), strv)
			}
			return true
		})
		this.checkProcessVar(sql, qryParam.GetParamVal)
	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstkeyname)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if innererr != nil {
		errcode = ERR_DB_ERR
		err = innererr
		return
	} else if err != nil {
		errcode = ERR_DB_ERR
		err = err
		return
	}

	return
}

/*
*

	"$sel":{
	      "type":1,
	       "__tracesql":1,   // 响应时返回sql
	      "fields":"id, name, uptime",

	       // 主体SQL语句,可以写视图,或者关联表, $field$将会替换 fields设置的值. 分离设定主要为了统计总记录数时进行替换成count(1), $table$ 可以通前端参数传递(args.table)
	      "sql":"select $fields$ from $table$ where fid='$id$' and ('$usrPrj$'='*' or tb.fproject in '$usrPrj$') $ORDERBY$ $PAGE$",
	      "jsonfields":""    // 指明字段中是否有json字段, 如果指明则响应数据时会处理成 json响应, 多个字段用 ","分割
	   },
*/
func (this *PlgDBBase) innerQryResType1(qryParam *PlgDBQryParam, sqlconf *wrapper.SuperValue) (errcode int, err error) {
	sql_templ := sqlconf.StringByName(fmt.Sprintf("sql-%s", qryParam.Dao.DriverName), "")
	if len(sql_templ) == 0 {
		sql_templ = sqlconf.StringByName("sql", "")
	}
	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	if qryParam.Resp == nil {
		qryParam.Resp = wrapper.NewSVObject()
	}

	dao := qryParam.Dao
	args := qryParam.Args
	resp := qryParam.Resp

	singlekeyname := qryParam.SingleName
	lstkeyname := DEFAULT_LIST_KEYNAME

	page := qryParam.ReqIntByName(KEY_PAGE_INDEX, -1)
	pagesize := qryParam.ReqIntByName(KEY_PAGE_SIZE, DEFAULT_PAGE_SIZE)
	reqtotal := qryParam.ReqIntByName(KEY_REQ_CNT, 1)
	qry_posi := qryParam.ReqIntByName(KEY_POSI, -1)
	qry_num := qryParam.ReqIntByName(KEY_POSI_NUM, DEFAULT_PAGE_SIZE)

	_, _, err = dao.QueryCustomEx(func() string {
		if len(singlekeyname) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func() string {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}
		return sql
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string, action int) {
		args.Range(func(key string, val *wrapper.SuperValue) bool {
			str := val.String()
			*sql = strings.ReplaceAll(*sql, fmt.Sprintf("$%s$", key), str)
			return true
		})

		if action == 1 {
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", "")
			*sql = strings.ReplaceAll(*sql, "$PAGE$", "")
		} else {
			var pagesql string = ""
			if qry_posi >= 0 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", qry_posi, qry_num)
			} else if page >= 1 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", (page-1)*pagesize, pagesize)
			}
			*sql = strings.ReplaceAll(*sql, "$PAGE$", pagesql)

			orderby := qryParam.ReqStringByName("orderby", "")
			if len(orderby) == 0 && sqlconf != nil {
				orderby = sqlconf.StringByName("orderby", "")
			}
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", orderby)
		}

		this.checkProcessVar(sql, qryParam.GetParamVal)

		if sqlconf.IntByName("__tracesql", 0) == 1 {
			resp.SetKeyValue("tracesql", *sql)
		}

	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstkeyname)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if err != nil {
		errcode = ERR_DB_ERR
		err = err
		return
	}

	return
}

/*
	"$sel":{
	      "__tracesql":1,    // 响应时返回sql
	      "type":2,
	      "fields":"id, name, uptime",

	       // 主体SQL语句,可以写视图,或者关联表, $field$将会替换 fields设置的值. 分离设定主要为了统计总记录数时进行替换成count(1)
	       // $CONDI$ 会被替换成组合条件, $ORDERBY$ 会替换成排序配置, $PAGE$ 替换成分页
	      "sql":"select $fields$ from goods WHERE 1=1 $CONDI$ $ORDERBY$ $PAGE$",
	      "args":{
	         "where":{     // where条件
	             "id":"fid='$id$'",      // $id$将会替换成传入的参数值, 如果传入args.id="001", 则会进行条件拼接  where fid='001'
	             "searchval":"name like '$searchval$%' or remark like '$searchval$'"
	          },
	         "__fixed":[    // 固定条件
	             "fstatus=1"
	          ]
	       },
	      "orderby":" id desc",         // 预置排序, 如果传入,则进行忽略
	      "jsonfields":""    // 指明字段中是否有json字段, 如果指明则响应数据时会处理成 json响应, 多个字段用 ","分割
	   },
*/
func (this *PlgDBBase) innerQryResType2(qryParam *PlgDBQryParam, sqlconf *wrapper.SuperValue) (errcode int, err error) {

	sql_templ := sqlconf.StringByName(fmt.Sprintf("sql-%s", qryParam.Dao.DriverName), "")
	if len(sql_templ) == 0 {
		sql_templ = sqlconf.StringByName("sql", "")
	}
	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	if qryParam.Resp == nil {
		qryParam.Resp = wrapper.NewSVObject()
	}

	dao := qryParam.Dao
	args := qryParam.Args
	resp := qryParam.Resp

	singlekeyname := qryParam.SingleName
	lstkeyname := qryParam.Req.StringByName("__list_name", DEFAULT_LIST_KEYNAME)

	page := qryParam.ReqIntByName(KEY_PAGE_INDEX, -1)
	pagesize := qryParam.ReqIntByName(KEY_PAGE_SIZE, DEFAULT_PAGE_SIZE)
	reqtotal := qryParam.ReqIntByName(KEY_REQ_CNT, 1)
	qry_posi := qryParam.ReqIntByName(KEY_POSI, -1)
	qry_num := qryParam.ReqIntByName(KEY_POSI_NUM, DEFAULT_PAGE_SIZE)

	_, _, err = dao.QueryCustomEx(func() string {
		if len(singlekeyname) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func() string {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}
		return sql
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string, action int) {

		// where条件
		sql_args_conf := sqlconf.ValueByName("args")
		wb := db_utils.NewSQLBuilder()
		{
			fixedCondi := wrapper.GetValueByName(sql_args_conf, "__fixed") // sql_args_conf.ValueByName("__fixed")
			if fixedCondi != nil {
				fixedCondi.Range(func(key string, val *wrapper.SuperValue) bool {
					wb.CheckAnd()
					wb.AddAny(val.String())
					return true
				})
			}
		}

		if args != nil && args.Count() > 0 {
			args_where_conf := wrapper.GetValueByName(sql_args_conf, "where") // sql_args_conf.ValueByName("where")
			if args_where_conf == nil {
				args_where_conf = sql_args_conf
			}
			if args_where_conf != nil {
				args_where_conf.Range(func(key string, val *wrapper.SuperValue) bool {
					if key == "__fixed" {
						return true
					}
					v1 := args.StringByName(key, "")
					if len(v1) == 0 {
						if ok, v2 := dao.GetParamVal(key); ok {
							v1 = fmt.Sprintf("%v", v2)
						}
					}
					if len(v1) > 0 {
						var str string
						if val.IsObject() {
							str = val.StringByName("value", "")
						} else {
							str = val.String()
						}
						str = strings.ReplaceAll(str, fmt.Sprintf("$%s$", key), v1)
						wb.CheckAnd()
						wb.AddAny(str)

					}
					return true
				})
			}

			args.Range(func(key string, val *wrapper.SuperValue) bool {
				str := val.String()
				*sql = strings.ReplaceAll(*sql, fmt.Sprintf("$%s$", key), str)
				return true
			})
		}
		if wb.Len() > 0 {
			strCondi := wb.String()
			*sql = strings.ReplaceAll(*sql, "$CONDI$", " AND "+strCondi)
		} else {
			*sql = strings.ReplaceAll(*sql, "$CONDI$", "")
		}

		// end where条件

		if action == 1 {
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", "")
			*sql = strings.ReplaceAll(*sql, "$PAGE$", "")
		} else {
			var pagesql string = ""
			if qry_posi >= 0 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", qry_posi, qry_num)
			} else if page >= 1 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", (page-1)*pagesize, pagesize)
			}
			*sql = strings.ReplaceAll(*sql, "$PAGE$", pagesql)

			orderby := qryParam.ReqStringByName("orderby", "")
			if len(orderby) == 0 && sqlconf != nil {
				orderby = sqlconf.StringByName("orderby", "")
			}
			if len(orderby) > 0 {
				orderby = "ORDER BY " + orderby
			}
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", orderby)
		}

		*sql = utils.ReplacePlaceholderFunc(*sql, "$", "$", qryParam.GetParamVal)

	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstkeyname)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if err != nil {
		errcode = ERR_DB_ERR
		err = err
		return
	}

	return
}
